// ================================================================================
// 		File: PartyGrassrootsGroupItem.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-06-11 23:56
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Grassroots
{
    /// <summary>
    /// 驻点小组
    /// </summary>   
    public class PartyGrassrootsGroupItem : EntityBase
    {
        #region 属性
        /// <summary>
        /// 组织ID
        /// </summary>
        public string BranchID { get; set; }
        /// <summary>
        /// 所属分组
        /// </summary>
        public string GroupID { get; set; }
        
        /// <summary>
        /// 管区
        /// </summary>
        public string TypeID { get; set; }

        /// <summary>
        /// 小组名称
        /// </summary>
        public string ItemName { get; set; }

        /// <summary>
        /// 驻点联系时间
        /// </summary>
        public string LinkTime { get; set; }

        /// <summary>
        /// 驻点联系地址
        /// </summary>
        public string LinkAddress { get; set; }

        /// <summary>
        /// 联系电话
        /// </summary>
        public string LinkMobileNo { get; set; }

        /// <summary>
        /// 联系人
        /// </summary>
        public string LinkMan { get; set; }

        /// <summary>
        /// 责任片区基本情况
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// 排序号
        /// </summary>
        public int OrderNo { get; set; }

        /// <summary>
        /// 状态(1-草稿，4-发布)
        /// </summary>
        public int State { get; set; }

        /// <summary>
        /// 创建人
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 修改人
        /// </summary>
        public string ModifyBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        #endregion

        #region 查询管理

        public static DataSet GetGridData(int pageIndex, int pageSize, string groupId = "", string searchKey = "",string branchID="")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.ID,data.GroupID,data.ItemName,data.BranchID,data.LinkMan,
data.LinkTime,data.LinkAddress,data.Description,data.OrderNo,data.State,
data.CreateBy,data.CreateTime,data.ModifyBy,data.ModifyTime,if(State=4,'发布','草稿') as StateName
                        from tblPartyGrassrootsGroupItem data
                        where 1=1 ");
            if (!string.IsNullOrEmpty(groupId))
            {
                sb.Append(" and data.groupId = ?groupId ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "groupId", Value = groupId });
            }
            if (!string.IsNullOrEmpty(branchID))
            {
                sb.Append(" and data.branchID = ?branchID ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "branchID", Value = branchID });
            }
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and data.ItemName like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }
            sb.Append(" order by data.OrderNo, data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataSet GetReportData(int pageIndex, int pageSize, string groupId, string itemId, string beginDate, string endDate, string branchId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };

            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS report.*,ItemName FROM tblpartygrassrootsgroupreport report
LEFT JOIN tblpartygrassrootsgroupitem item ON report.ItemID=item.ID
LEFT JOIN tblpartygrassrootsgroup g ON item.GroupID=g.ID
                        where 1=1 ");
            if (!string.IsNullOrEmpty(groupId))
            {
                sb.Append(" and g.ID=?GroupID");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "GroupID", Value = groupId });
            }
            if (!string.IsNullOrEmpty(itemId))
            {
                sb.Append(" and report.ItemID=?ItemID");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "ItemID", Value = itemId });
            }
            if (!string.IsNullOrEmpty(beginDate))
            {
                sb.Append(" and DATE_FORMAT(report.ReportTime,'%Y-%m')>=?BeginDate");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "BeginDate", Value = beginDate });
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                sb.Append(" and DATE_FORMAT(report.ReportTime,'%Y-%m')<=?EndDate");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "EndDate", Value = endDate });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and item.BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID)");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }

            sb.Append(" order by report.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataSet QueryItemDataByGroupID(string groupID, string branchID = "")
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@"select * from tblpartygrassrootsgroupitem where GroupID=?GroupID ");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "GroupID", Value = groupID });

            if (!string.IsNullOrEmpty(branchID))
            {
                sbSql.Append(@" and BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID) ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchID });
            }

            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 根据公司/社区标识ID获取小组信息
        /// </summary>
        /// <param name="ItemID"></param>
        /// <returns></returns>
        public static DataTable QueryGroupDataByItemID(string itemId)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@" select GroupID from tblpartygrassrootsgroupitem where ID=?ItemID");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ItemID", Value = itemId });
            return MySqlHelper.ExecuteDataTable(sbSql.ToString(), parameters.ToArray());
        }
        // <summary>
        /// 根据公司/社区标识ID获取小组信息
        /// </summary>
        /// <param name="ItemID"></param>
        /// <returns></returns>
        public static string QueryAllNameByItemID(string itemId)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@" select CONCAT(g.GroupName,'-',i.ItemName)  from tblpartygrassrootsgroup g 
left join tblpartygrassrootsgroupitem i on i.groupid=g.id where i.ID=?ItemID");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ItemID", Value = itemId });
            return MySqlHelper.ExecuteScalar(sbSql.ToString(), parameters.ToArray()) as string;
        }
        /// <summary>
        /// 根据公司/社区标识ID获取小组ID
        /// </summary>
        /// <param name="ItemID"></param>
        /// <returns></returns>
        public static string QueryGroupIDByItemID(string ItemID)
        {
            DataTable dt = QueryGroupDataByItemID(ItemID);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["GroupID"].ToString();
            }
            else return "";
        }

        /// <summary>
        /// 获取GetDataGrid数据源(分页)
        /// </summary>
        /// <returns></returns>
        public static int GetAllCount()
        {
           
                StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"select Count(*) from tblPartyGrassrootsGroupItem data
                            where data.GroupID in 
                            (select ID from tblPartyGrassrootsGroup g where g.State=4)
                            and data.State=4");
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(sbSql.ToString()));
        }
        
        #endregion
    }
}
